﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace Dashboard.ProductBonus.Business
{
    public class WorkingSchedule : BusinessBase
    {
        public DataTable GetScheduleByTeam(string team, string shift, string dateFrom, string dateTo)
        {
            string sql =
                "select a.Team,a.Shift,a.WorkingDate,b.id as WorkingShift from TeamShiftSchedule a, WorkingShift b " +
                " where a.workingshift=b.id " +
                " and workingdate>='" + dateFrom + "' and workingdate<='" + dateTo + "'";
            if (!string.IsNullOrEmpty(team))
            {
                sql = sql + " and team='" + team + "'";
            }
            if (!string.IsNullOrEmpty(shift))
            {
                sql = sql + " and a.shift='" + shift + "'";
            }
            sql += " order by a.team,a.shift,a.WorkingDate";
            return DbConnection.DataSet(sql).Tables[0];
        }

        public void SaveTeamData(Dictionary<string, Dictionary<string, string>> data, string loginUser)
        {
            foreach (var team in data.Keys)
            {
                foreach (var date in data[team].Keys)
                {
                    string sqlDelete = "delete from TeamShiftSchedule where team='{0}' and shift='{1}' and WorkingDate='{2}'";
                    sqlDelete = string.Format(sqlDelete, team.Split('|')[0], team.Split('|')[1], date);
                    DbConnection.ExecuteSql(sqlDelete);
                    string sqlInsert =
                        @"INSERT INTO [TeamShiftSchedule]
                           ([Team]
                           ,[Shift]
                           ,[WorkingDate]
                           ,[WorkingShift]
                           ,[InsertBy]
                           ,[InsertDate]
                           ,[UpdatedBy]
                           ,[UpdatedDate])
                     VALUES
                           ('{0}'
                           ,'{1}'
                           ,'{2}'
                           ,'{3}'
                           ,'{4}'
                           ,getdate()
                           ,'{5}'
                           ,getdate())";
                    var workingShift = data[team][date];
                    if (string.IsNullOrEmpty(workingShift))
                    {
                        workingShift = "-1";
                    }
                    sqlInsert = string.Format(sqlInsert, team.Split('|')[0], team.Split('|')[1], date, workingShift, loginUser, loginUser);
                    DbConnection.ExecuteSql(sqlInsert);

                    string sqlDeleteWorker =
                        @"delete from [WorkerShiftSchedule] from [WorkerShiftSchedule] w inner join  
                        (select staffNo,workingDate,b.team,b.shift from namelist a,TeamShiftSchedule b
                          where a.team=b.team and a.shift=b.shift and b.WorkingDate>=isnull(a.joindate,'1970-01-01')) x on x.StaffNo=w.StaffNo and w.workingdate=x.workingdate
                        and x.WorkingDate>='{0}' and x.WorkingDate<='{1}' and x.Team='{2}' and x.Shift='{3}'";

                    sqlDeleteWorker = string.Format(sqlDeleteWorker, date, date, team.Split('|')[0], team.Split('|')[1]);
                    DbConnection.ExecuteSql(sqlDeleteWorker);

                    string sqlInsertWorker =
                        @"INSERT INTO [WorkerShiftSchedule]
                           ([StaffNo]
                           ,[WorkingDate]
                           ,[WorkingShift]
                           ,[InsertBy]
                           ,[InsertDate]
                           ,[UpdatedBy]
                           ,[UpdatedDate])
                          select staffNo,workingDate,WorkingShift,'{0}',getdate(),'{1}',getdate() from namelist a,TeamShiftSchedule b
                          where a.team=b.team and a.shift=b.shift and b.WorkingDate>=isnull(a.joindate,'1970-01-01') and b.WorkingDate>='{2}' and b.WorkingDate<='{3}' and b.Team='{4}' and b.shift='{5}'";
                    sqlInsertWorker = string.Format(sqlInsertWorker, loginUser, loginUser, date, date, team.Split('|')[0], team.Split('|')[1]);
                    DbConnection.ExecuteSql(sqlInsertWorker);
                }
            }
        }

        public DataTable LoadWorkerSchedule(string team, string shift, DateTime dateFrom, DateTime dateTo)
        {
            string sql =
                @"SELECT a.[StaffNo],isnull(a.[JoinDate],'1970-01-01') as JoinDate,a.[Name] as StaffName,a.[Team],a.[Shift]
                              ,b.[WorkingDate]
                              ,b.[WorkingShift]
                          FROM NameList a left outer join [WorkerShiftSchedule] b
                          on a.staffno=b.staffno and b.WorkingDate>='{0}' and b.WorkingDate<='{1}'
                          WHERE a.team='{2}' and a.Shift='{3}' and isnull(a.Leave,'N')<>'Y'";
            sql = string.Format(sql, dateFrom.ToString("yyyy-MM-dd"), dateTo.ToString("yyyy-MM-dd"), team, shift);
            return DbConnection.DataSet(sql).Tables[0];
        }

        public void SaveWorkerData(Dictionary<string, Dictionary<string, string>> data, string loginUser)
        {
            foreach (var staff in data.Keys)
            {
                foreach (var date in data[staff].Keys)
                {
                    string sqlDelete = "delete from WorkerShiftSchedule where staffno='{0}' and WorkingDate='{1}'";
                    sqlDelete = string.Format(sqlDelete, staff, date);
                    DbConnection.ExecuteSql(sqlDelete);
                    string sqlInsert =
                        @"INSERT INTO [WorkerShiftSchedule]
                           ([StaffNo]
                           ,[WorkingDate]
                           ,[WorkingShift]
                           ,[InsertBy]
                           ,[InsertDate]
                           ,[UpdatedBy]
                           ,[UpdatedDate])
                     VALUES
                           ('{0}'
                           ,'{1}'
                           ,{2}
                           ,'{3}'
                           ,getdate()
                           ,'{4}'
                           ,getdate())";
                    var workingShift = data[staff][date];
                    if (string.IsNullOrEmpty(workingShift))
                    {
                        workingShift = "-1";
                    }
                    sqlInsert = string.Format(sqlInsert, staff, date, workingShift, loginUser, loginUser);
                    DbConnection.ExecuteSql(sqlInsert);
                }
            }
        }

        //end of class
    }
}
